-- @owner: zhanghuan96
-- @date: 2024-10-09
-- @testpoint: 创建100张表，对比选项有序排列及无序重复排列
@conn PrimaryDbBmode;
--step1:创建函数，建表语句中可选项顺序排列;expect:成功
drop  FUNCTION if exists public.create_tables;
CREATE OR REPLACE FUNCTION public.create_tables(
    table_num_in integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
declare
v_table_num integer  :=100;
v_idx integer := 0;
v_strTable varchar :='';
v_strSql varchar :='';

begin
  while v_idx < table_num_in loop
  v_idx = v_idx+1;
  v_strTable = CONCAT('t_option', v_idx);
  v_strSql = 'create table '||v_strTable||'(idx integer primary key,log varchar(20))
  with(orientation=row,fillfactor=25) nocompress tablespace pg_default engine= 表';
  EXECUTE v_strSql;
  end loop;
 end
$BODY$;
/
--step2:调用函数建100张表;expect:成功
SELECT public.create_tables(100);
select count(*) from pg_class where relname like 't_option%';
--step3:创建删表函数;expect:成功
drop  FUNCTION if exists public.drop_tables;
CREATE OR REPLACE FUNCTION public.drop_tables(
    table_num_in integer)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$

declare
v_idx integer := 0;
v_strTable varchar :='';
v_strSql varchar :='';
begin
  while v_idx < table_num_in loop
  v_idx = v_idx+1;
  v_strTable = CONCAT('t_option', v_idx);
  v_strSql = 'drop table if exists '||v_strTable;
  EXECUTE v_strSql;
  end loop;
 end
$BODY$;
/
--step4:调用函数删表;expect:成功
SELECT public.drop_tables(100);

--step5:创建函数，建表语句中可选项无序及重复排列;expect:成功
drop FUNCTION if exists public.create_tables;
CREATE OR REPLACE FUNCTION public.create_tables(
    table_num_in integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
declare
v_table_num integer  :=100;
v_idx integer := 0;
v_strTable varchar :='';
v_strSql varchar :='';

begin
  while v_idx < table_num_in loop
  v_idx = v_idx+1;
  v_strTable = CONCAT('t_option', v_idx);
  v_strSql = 'create table '||v_strTable||'(idx integer primary key,log varchar(20))
  nocompress with(orientation=row,fillfactor=25) tablespace pg_default engine= 表';
  EXECUTE v_strSql;
  end loop;
 end
$BODY$;
/
--step6:调用函数建表;expect:成功
SELECT public.create_tables(100);
select count(*) from pg_class where relname like 't_option%';
--step7:创建删表函数;expect:成功
drop FUNCTION if exists public.drop_tables;
CREATE OR REPLACE FUNCTION public.drop_tables(
    table_num_in integer)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$

declare
v_idx integer := 0;
v_strTable varchar :='';
v_strSql varchar :='';
begin
  while v_idx < table_num_in loop
  v_idx = v_idx+1;
  v_strTable = CONCAT('t_option', v_idx);
  v_strSql = 'drop table if exists '||v_strTable;
  EXECUTE v_strSql;
  end loop;
 end
$BODY$;
/
--step8:调用函数删表;expect:成功
SELECT public.drop_tables(100);
--step9:清理环境
drop FUNCTION if exists public.create_tables;
drop FUNCTION if exists public.drop_tables;